﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Shared.Method
{
    public class SQLServerContext
    {
        private SqlConnection _sqlConn = null;
        /// <summary>
        /// 连接数据库名称
        /// </summary>
        public string DatabaseName { get { return _sqlConn.Database; } }

        //private object _lockObj = new object();//锁定 isUse
        //public bool IsUse { get; set; }//给isUse赋值

        private SqlTransaction _tran = null;
        //private DateTime _tranTime;
        private SqlCommand _tranCmd = null;
        //private readonly object objLock = new object();
        private System.Threading.Semaphore _tranSem = new System.Threading.Semaphore(1, 1);

        //done transcation only
        public bool TranscationType { get; private set; }

        public SQLServerContext(string strConn, bool transcationType = false)//连接的数据库名称
        {
            if (string.IsNullOrEmpty(strConn))//初始化连接数据库名称
            {
                LogHelper.WriteErrLogTxt("SQLServerCommand()", "连接地址为空", "连接地址为空");
                return;
            }
            _sqlConn = new SqlConnection(strConn);//初始化连接
            TranscationType = transcationType;
            //IsUse = true;
        }
        /// <summary>
        /// 获得sql的参数信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static SqlParameter CreateParameter<T>(string name, T value)
        {
            var param = new SqlParameter(name, value);
            if (value == null)//解决参数值，可以解决数据为空的问题
            {
                param.Value = DBNull.Value;
            }
            //解决参数类型问题

            return param;
        }

        #region test transcation

        private void TaskCommin()
        {
            System.Threading.Tasks.Task.Run(() =>
            {
                System.Threading.Thread.Sleep(1000);

                _tranSem.WaitOne();

                if (_tran != null)
                {
                    try
                    {
                        _tran.Commit();
                    }
                    catch
                    {
                        _tran.Rollback();
                    }
                    _tran = null;
                }

                _tranSem.Release();
            });
        }

        public int TestTrainExec(string sql, SqlParameter[] paras, CommandType type)
        {
            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                _tranSem.WaitOne();

                if (_tran == null)
                {
                    _tran = _sqlConn.BeginTransaction(IsolationLevel.ReadCommitted);

                    _tranCmd = new SqlCommand()
                    {
                        CommandType = type,
                        Connection = _sqlConn,
                        Transaction = _tran
                    };

                    TaskCommin();
                }

                _tranCmd.CommandText = sql;

                if (paras != null)
                    _tranCmd.Parameters.AddRange(paras);

                int result = _tranCmd.ExecuteNonQuery();

                _tranSem.Release();

                //IsUse = false;
                return result;
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }


        public T TestTrainGetFirstCell<T>(string sql, SqlParameter[] paras, CommandType type)
        {
            T result;

            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                _tranSem.WaitOne();

                if (_tran == null)
                {
                    _tran = _sqlConn.BeginTransaction(IsolationLevel.ReadCommitted);

                    _tranCmd = new SqlCommand()
                    {
                        CommandType = type,
                        Connection = _sqlConn,
                        Transaction = _tran
                    };

                    TaskCommin();
                }

                _tranCmd.CommandText = sql;

                if (paras != null)
                    _tranCmd.Parameters.AddRange(paras);

                var resultObj = _tranCmd.ExecuteScalar();

                _tranSem.Release();

                if (resultObj == DBNull.Value) result = default(T);//若res为DBNULL，则result为default(T)
                else result = (T)resultObj;//强制转换类型

                //IsUse = false;
                return result;
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }

        #endregion


        /// <summary>
        /// 执行没有返回值的SQL语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras">sql的参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, SqlParameter[] paras, CommandType type = CommandType.Text)
        {
            //return TestTrainExec(sql, paras, type);

            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                SqlCommand cmd = new SqlCommand(sql, _sqlConn);
                cmd.CommandType = type;//设置执行类型
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                int result = cmd.ExecuteNonQuery();
                cmd.Dispose();//销毁执行命令
                _sqlConn.Close();//关闭连接

                //IsUse = false;
                return result;
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }

        //获得单个数据-数量
        public T GetFirstCell<T>(string sql, SqlParameter[] paras, CommandType cmdType = CommandType.Text)
        {
            //return TestTrainGetFirstCell<T>(sql, paras, cmdType);

            T result;
            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                SqlCommand cmd = new SqlCommand(sql, _sqlConn)
                {
                    CommandType = cmdType
                };
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                var res = cmd.ExecuteScalar();//返回结果集的第一行/第一列

                if (res == DBNull.Value) result = default(T);//若res为DBNULL，则result为default(T)
                else result = (T)res;//强制转换类型

                cmd.Dispose();//销毁执行命令
                _sqlConn.Close();//关闭连接

                //IsUse = false;
                return result;
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// 获取指定条件下一列数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public List<T> GetColumnList<T>(string sql, SqlParameter[] paras, CommandType cmdType = CommandType.Text)
        {
            List<T> result = new List<T>();//定义结果
            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                SqlCommand cmd = new SqlCommand(sql, _sqlConn);
                cmd.CommandType = cmdType;
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                using (var reader = cmd.ExecuteReader())
                {
                    //获得所有的PropertyInfo
                    while (reader.Read())//顺序获得数据
                    {
                        result.Add((T)reader[0]);
                    }

                    reader.Close();
                }
                cmd.Dispose();//销毁执行命令
                _sqlConn.Close();//关闭连接


                //IsUse = false;
                return result;
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// 查询某条件下的结果集
        /// </summary>
        /// <typeparam name="T">结果集类</typeparam>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>返回结果集类</returns>
        public List<T> GetTableList<T>(string sql, SqlParameter[] paras) where T : new()
        {
            List<T> result = new List<T>();//定义结果
            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                SqlCommand cmd = new SqlCommand(sql, _sqlConn);
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                using (var reader = cmd.ExecuteReader())
                {
                    //获得所有的PropertyInfo
                    var properties = typeof(T).GetProperties();
                    while (reader.Read())//顺序获得数据
                    {
                        T value = new T();
                        for (int i = 0; i < properties.Length; i++)//给每个类赋值
                        {
                            try
                            {
                                if (reader[properties[i].Name] == DBNull.Value)
                                {
                                    properties[i].SetValue(value, default(T), null);//从NULL修改为default(T)
                                }
                                else
                                {
                                    properties[i].SetValue(value, reader[properties[i].Name], null);//给每个元素赋值
                                }
                            }
                            catch
                            {
                                properties[i].SetValue(value, default(T), null);//从NULL修改为default(T)
                            }
                        }
                        result.Add(value);
                    }

                    reader.Close();
                }
                cmd.Dispose();//销毁执行命令
                _sqlConn.Close();//关闭连接

                //IsUse = false;
                return result;
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }

        public string GetDatatableJson(string sql, SqlParameter[] paras)
        {
            DataTable table = new DataTable();

            try
            {
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();//打开连接

                SqlCommand cmd = new SqlCommand(sql, _sqlConn);
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                using (var reader = cmd.ExecuteReader())
                {
                    //add columns to datatable
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        table.Columns.Add(new DataColumn(reader.GetName(i)));
                    }

                    while (reader.Read())//顺序获得数据
                    {
                        DataRow row = table.NewRow();
                        for (int j = 0; j < reader.FieldCount; j++)
                        {
                            row[j] = reader[j];
                        }
                        table.Rows.Add(row);
                    }

                    reader.Close();
                }
                cmd.Dispose();//销毁执行命令
                _sqlConn.Close();//关闭连接

                //IsUse = false;

                return JsonConvert.SerializeObject(table);
            }
            catch (Exception ex)
            {
                _sqlConn.Close();//关闭连接
                //IsUse = false;
                throw new Exception(ex.ToString());
            }
        }
    }
}
